package Model.OrcUtils;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

import org.apache.commons.beanutils.BeanUtils;

public class JDBCUtils {
    public static Connection getConnection() throws IOException, SQLException {
        Properties properties = new Properties();
        properties.load(
                new FileInputStream("kabuto/src/Model/OrcUtils/Login_Initial.properties")
        );
        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password= properties.getProperty("password");
        return DriverManager.getConnection(url,username,password);
    }

    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection conn, PreparedStatement ps){
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static int DataChange(String sql,Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for (int i = 0;i < args.length; i++){
                //自动识别判断Objects 数据类型
                //给占位赋值
                ps.setObject(i+1,args[i]);
            }
            count = ps.executeUpdate();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,ps);
        }
        return count;
    }

    public static <T> List<T> JDBCselect(String sql,Class<T> cls,Object... args){
        List<T> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++){
                ps.setObject(i + 1,args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            int count = rsd.getColumnCount();
            while(rs.next()){
                T t = cls.newInstance();
                Map<String,Object> map = new HashMap<>();
                for (int i = 0;i < count; i++){
                    String column = rsd.getColumnLabel(i+1);
                    Object value = rs.getObject(column);
                    map.put(column,value);
                }
                BeanUtils.populate(t,map);
                list.add(t);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e){
            e.printStackTrace();
        } catch (IllegalAccessException e){
            e.printStackTrace();
        } catch (InvocationTargetException e){
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,ps,rs);
        }
        return list;
    }

    public static List<String> getHead(String sql) {
        List<String> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            int count = rsd.getColumnCount();
            while(rs.next()){
                for (int i = 0;i < count; i++){
                    String column = rsd.getColumnLabel(i+1);
                    Object value = rs.getObject(column);
                    if (list.contains(column)){
                        break;
                    } else {
                        list.add(column);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,ps,rs);
        }
        return list;
    }

    public static List<String[]> getDataList(String sql) {
        List<String[]> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            int count = rsd.getColumnCount();
            while(rs.next()){
                List<String> list1 = new ArrayList<>();
                for (int i = 0;i < count; i++){
                    String column = rsd.getColumnLabel(i+1);
                    Object value = rs.getObject(column);
                    list1.add(String.valueOf(value));
                }
                String[] strings = new String[list1.size()];
                for(int i=0;i< list1.size();i++){
                    strings[i] = list1.get(i);
                }
                list.add(strings);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,ps,rs);
        }
        return list;
    }
}
